package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.sg.hms.Dao.H_MoneyAppliDao;
import com.sg.hms.Model.H_moneyapplication;
import com.sg.hms.Util.JDBCUtil;

public class H_MoneyAppliDaoImpl implements H_MoneyAppliDao{

	@Override
	public List<H_moneyapplication> selectFinishMoneyAppli(int start,int pagesize) {
		// TODO Auto-generated method stub
		List<H_moneyapplication> list=new ArrayList<H_moneyapplication>();
		Connection con=JDBCUtil.getConnection();
		//StringBuffer sql=new StringBuffer("select * from moneyapplication where state<>'已提交' limit ?,?");
		StringBuffer sql=new StringBuffer("select C.maId,C.money,C.state,C.approveDate,C.createDate,C.aId,D.elName,E.dName,C.aContent");
		sql.append(" from ( ");
		sql.append(" select A.maId,A.money,A.state,A.approveDate,A.createDate,A.aId,B.elId,B.aContent");
		sql.append(" from (select * from moneyapplication where state not in('已提交','已回复') limit ?,?) A,application B ");
		sql.append(" where A.aId=B.aId");
		sql.append(" ) C,employee D,department E");
		sql.append(" where C.elId=D.elId and D.dId=E.dId");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pagesize);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				
				H_moneyapplication ma=new H_moneyapplication();
				ma.setMaId(rs.getInt("maId"));
				ma.setaId(rs.getInt("aId"));
				ma.setElName(rs.getString("elName"));
				ma.setdName(rs.getString("dName"));
				ma.setaContent(rs.getString("aContent"));
				ma.setMoney(rs.getDouble("money"));
				ma.setState(rs.getString("state"));
				Date cdate=rs.getDate("createDate");
				Date adate=rs.getDate("approveDate");
				java.util.Date cd=new java.util.Date(cdate.getTime());
				java.util.Date ad=new java.util.Date(adate.getTime());
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String creatdate=sdf.format(cd);
				String approveDate=sdf.format(ad);
				ma.setCreateDate(creatdate);
				ma.setApproveDate(approveDate);
			    list.add(ma);
				
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int getFinishMoneyApplisum() {
		// TODO Auto-generated method stub
		int sum=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select count(maId) sum from moneyapplication where state not in('已提交','已回复')");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				sum=rs.getInt("sum");
			}
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sum;
	}

	@Override
	public int updateMoneyAppliState(String state,int maId) {
		// TODO Auto-generated method stub
		int flag=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("update moneyapplication set state=? where maId=?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, state);
			pst.setInt(2, maId);
			int i=pst.executeUpdate();
			if(i>0){
				flag=1;
			}
			
			pst.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

}
